USE [IUDICIUM]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteBySearch]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTramiteBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetTramiteBySearch]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetProcesoById]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetProcesoById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetProcesoById]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetProcesoBySearch]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetProcesoBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetProcesoBySearch]
GO
/****** Object:  View [dbo].[uv_Responsables]    Script Date: 06/21/2012 16:54:28 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[uv_Responsables]'))
DROP VIEW [dbo].[uv_Responsables]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_DeleteProceso]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_DeleteProceso]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_DeleteProceso]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_InsertProceso]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_InsertProceso]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_InsertProceso]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_UpdateProceso]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_UpdateProceso]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_UpdateProceso]
GO
/****** Object:  View [dbo].[uv_Involucrados]    Script Date: 06/21/2012 16:54:28 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[uv_Involucrados]'))
DROP VIEW [dbo].[uv_Involucrados]
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]
GO
/****** Object:  UserDefinedFunction [dbo].[uf_GetDemandadosList]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetDemandadosList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetDemandadosList]
GO
/****** Object:  UserDefinedFunction [dbo].[uf_GetDemandantesList]    Script Date: 06/21/2012 16:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetDemandantesList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetDemandantesList]
GO
/****** Object:  UserDefinedFunction [dbo].[uf_GetDemandantesList]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetDemandantesList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[uf_GetDemandantesList]
(
	@intServicioId	INT
)
RETURNS VARCHAR(500)
AS
BEGIN

	DECLARE @listStr VARCHAR(MAX)
	
	SELECT @listStr = COALESCE(@listStr+'', '' ,'''') + [nombre]
		FROM	[dbo].[tbl_PersonaNatural]pn,
				[dbo].[tbl_Persona]p,
				[dbo].[tbl_Involucrado]i,
				[dbo].[tbl_Servicio]s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pn.PersonaNaturalId
				AND
				i.tipoInvolucrado=''Demandante''
	
		SELECT @listStr = COALESCE(@listStr+'', '' ,'''') + [razonSocial]
		FROM	[dbo].[tbl_PersonaJuridica]pj,
				[dbo].[tbl_Persona]p,
				[dbo].[tbl_Involucrado]i,
				[dbo].[tbl_Servicio]s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pj.PersonaJuridicaId
				AND
				i.tipoInvolucrado=''Demandante''
			
	return @listStr	

END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[uf_GetDemandadosList]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetDemandadosList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[uf_GetDemandadosList]
(
	@intServicioId	INT
)
RETURNS VARCHAR(500)
AS
BEGIN

	DECLARE @listStr VARCHAR(MAX)
	
	SELECT @listStr = COALESCE(@listStr+'', '' ,'''') + [nombre]
		FROM	[dbo].[tbl_PersonaNatural]pn,
				[dbo].[tbl_Persona]p,
				[dbo].[tbl_Involucrado]i,
				[dbo].[tbl_Servicio]s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pn.PersonaNaturalId
				AND
				i.tipoInvolucrado=''Demandado''
	
		SELECT @listStr = COALESCE(@listStr+'', '' ,'''') + [razonSocial]
		FROM	[dbo].[tbl_PersonaJuridica]pj,
				[dbo].[tbl_Persona]p,
				[dbo].[tbl_Involucrado]i,
				[dbo].[tbl_Servicio]s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pj.PersonaJuridicaId
				AND
				i.tipoInvolucrado=''Demandado''
			
	return @listStr	

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 23/03/2012
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetTipoProcesoByIdEtapa]
	@inttipo int
AS
BEGIN

	SET NOCOUNT ON;

     SELECT c.[tipoProcesoId]
      ,c.[descripcion]
	FROM [IUDICIUM].[dbo].[tbl_TipoProceso] as c,
	[IUDICIUM].[dbo].[tbl_EtapaProceso]as t
	WHERE  
	t.[etapaProcesoId]=@inttipo
	AND
	c.tipoProcesoId=t.tipoProcesoId
    END
' 
END
GO
/****** Object:  View [dbo].[uv_Involucrados]    Script Date: 06/21/2012 16:54:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[uv_Involucrados]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[uv_Involucrados]
AS
SELECT i.[involucradoId], p.[personaId], pn.[nombre]
	FROM [dbo].[tbl_Involucrado] i 
		JOIN [dbo].[tbl_Persona] p ON p.[personaId] = i.[personaId]
		JOIN [dbo].[tbl_PersonaNatural] pn ON pn.[personaNaturalId] = p.[personaId]
	UNION
	SELECT i.[involucradoId], p.[personaId], pj.[razonSocial]
	FROM [dbo].[tbl_Involucrado] i 
		JOIN [dbo].[tbl_Persona] p ON p.[personaId] = i.[personaId]
		JOIN [dbo].[tbl_PersonaJuridica] pj ON pj.[personaJuridicaId] = p.[personaId]
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'uv_Involucrados', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "tbl_Involucrado"
            Begin Extent = 
               Top = 163
               Left = 248
               Bottom = 282
               Right = 446
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tbl_PersonaNatural"
            Begin Extent = 
               Top = 6
               Left = 274
               Bottom = 125
               Right = 472
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tbl_PersonaJuridica"
            Begin Extent = 
               Top = 0
               Left = 739
               Bottom = 104
               Right = 937
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tbl_Persona"
            Begin Extent = 
               Top = 114
               Left = 510
               Bottom = 203
               Right = 708
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 13' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'uv_Involucrados'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'uv_Involucrados', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'50
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'uv_Involucrados'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'uv_Involucrados', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'uv_Involucrados'
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_UpdateProceso]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_UpdateProceso]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 21/03/2012
-- Description:	actualiza un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_UpdateProceso]
	@intProcesoId				int,
	@numeroexpe					varchar(50),
	@montodemanda				decimal(10,2),
	@fechaEntregaEstimada		datetime,	
	@observacion				varchar(250), 
	@intEtapaProcesoId			int,
	@varInvolucradosInsertados	varchar(4000),
	@varInvolucradosEliminados	varchar(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION
    
    BEGIN TRY
		
		UPDATE
				[dbo].[tbl_Servicio]
				SET
				[observacion]=@observacion,
				[fechaEntregaEstimada] = @fechaEntregaEstimada
		WHERE	[servicioId] = @intProcesoId
		PRINT ''Se actualizo servicio''
		UPDATE	[dbo].[tbl_Proceso]
			SET	[etapaProcesoId] = @intEtapaProcesoId,
				[numeroExpediente]=@numeroexpe,
				[montoDemanda]=@montodemanda
		WHERE	[ProcesoId] = @intProcesoId
		PRINT ''Se actualizo Proceso''
		
		IF NOT @varInvolucradosEliminados IS NULL AND @varInvolucradosEliminados <> ''''
		BEGIN
			
			DELETE FROM [dbo].[tbl_Involucrado]
			WHERE	[servicioId] = @intProcesoId
					AND [personaId] IN
					(SELECT CAST([splitValue] AS INT) FROM [dbo].[uf_SplitString](@varInvolucradosEliminados,'',''))
			
			PRINT ''Se elimino involucrados''
		END
		
		IF NOT @varInvolucradosInsertados IS NULL AND @varInvolucradosInsertados <> ''''
		BEGIN
			
			INSERT INTO [dbo].[tbl_Involucrado]
			   ([personaId]
			   ,[servicioId]
			   ,[tipoInvolucrado])
			   SELECT	CAST([splitValue] AS INT),
						@intProcesoId,
						''Participante''
			   FROM [dbo].[uf_SplitString](@varInvolucradosInsertados,'','')
			
			PRINT ''Se guardaron los involucrados''
		END

		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
    
		ROLLBACK TRANSACTION
		PRINT ''Se hizo rollback''
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
    
    END CATCH
    
    
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_InsertProceso]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_InsertProceso]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 21/03/2012
-- Description:	inserta un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_InsertProceso]
	@intProcesoId			INT OUTPUT,
	@numeroexpe				VARCHAR(50),
	@montodemanda			DECIMAL(10,2),
	@codigoProceso			VARCHAR(50) OUTPUT,
	@etapaProcesoId			INT,
	@fechaInicio			DATETIME,
	@fechaFin				DATETIME,
	@fechaEntregaEstimada	DATETIME,
	@observacion			VARCHAR(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION
    
    BEGIN TRY
    
		EXEC [dbo].[usp_SERVICIO_GetNextCode]
			''Proceso'',
			@codigoProceso OUTPUT
		
    
		INSERT INTO [dbo].[tbl_Servicio]
           ([tipoServicio]
           ,[codigoServicio]
           ,[fechaInicio]
           ,[fechaFin]
           ,[fechaEntregaEstimada]
           ,[observacion]
           ,[estado]
           ,[fechaEntrega]
           ,[entregadoA]
           ,[observacionEntrega])
		 VALUES
			   (''Proceso''
			   ,@codigoProceso
			   ,@fechainicio
			   ,NULL	
			   ,@fechaEntregaEstimada
			   ,@observacion
			   ,''En Proceso''
			   ,NULL
			   ,''''
			   ,'''')

		SET @intProcesoId = SCOPE_IDENTITY()

		INSERT INTO [dbo].[tbl_Proceso]
           ([ProcesoId]
           ,[etapaProcesoId]
           ,[numeroExpediente]
           ,[montoDemanda])
		 VALUES
		   (@intProcesoId
		   ,@etapaProcesoId
		   ,@numeroexpe
		   ,@montodemanda)
		
		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
    
		ROLLBACK TRANSACTION
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
    
    END CATCH
    
    
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_DeleteProceso]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_DeleteProceso]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 21/03/2012
-- Description:	elimina un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_DeleteProceso]
	@intProcesoId	int
AS
BEGIN

	SET NOCOUNT ON;

    BEGIN TRANSACTION
    
    BEGIN TRY

		DELETE 
		FROM	[dbo].[tbl_Proceso]
		WHERE	[ProcesoId] = @intProcesoId
		
		DELETE
		FROM	[dbo].[tbl_Servicio]
		WHERE	[servicioId]=@intProcesoId
		
		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
    
		ROLLBACK TRANSACTION
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
    
    END CATCH
    END

' 
END
GO
/****** Object:  View [dbo].[uv_Responsables]    Script Date: 06/21/2012 16:54:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[uv_Responsables]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[uv_Responsables]
AS
SELECT     r.responsableId, pn.personaNaturalId, pn.nombre
FROM         dbo.tbl_Responsable AS r INNER JOIN
                      dbo.tbl_Administrativo AS a ON a.administrativoId = r.administrativoId INNER JOIN
                      dbo.tbl_PersonaNatural AS pn ON pn.personaNaturalId = a.administrativoId
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'uv_Responsables', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "r"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 125
               Right = 236
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "a"
            Begin Extent = 
               Top = 6
               Left = 274
               Bottom = 110
               Right = 472
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "pn"
            Begin Extent = 
               Top = 6
               Left = 510
               Bottom = 125
               Right = 708
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'uv_Responsables'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'uv_Responsables', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'uv_Responsables'
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetProcesoBySearch]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetProcesoBySearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 21/03/2012
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetProcesoBySearch]
	@varParametroBusqueda	NVARCHAR(4000),
	@bitTienePermiso		BIT,
	@intUsuarioId			INT
AS
BEGIN
	SET NOCOUNT ON;
    IF(@varParametroBusqueda IS NULL OR @varParametroBusqueda = '''')
		SET @varParametroBusqueda = ''''
		
	DECLARE @tblServicios AS TABLE([id] INT)
	IF @bitTienePermiso = 0
	BEGIN
	
		INSERT INTO @tblServicios ([id])
			SELECT [servicioId] FROM [dbo].[tbl_Responsable] WHERE [administrativoId] = @intUsuarioId AND [estado] = ''Activo''
	END
    SELECT
			[ProcesoId]
			,[codigoServicio]
			,p.[etapaProcesoId]
			,[numeroExpediente]
			,[montoDemanda]
			,ISNULL([fechaInicio],'''') AS [fechaInicio]
			,[fechaEntregaEstimada]			
			,[fechaFin]
			,[observacion]
			,[estado]
			,ISNULL([fechaEntrega],'''') AS [fechaEntrega]
			,ISNULL([entregadoA],'''') AS [entregadoA]
			,ISNULL([observacionEntrega],'''') AS [observacionEntrega]
			,ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'''') AS [responsables]
			,ISNULL([dbo].[uf_GetDemandadosList](s.[servicioId]),'''') AS [demandados]
			,ISNULL([dbo].[uf_GetDemandantesList](s.[servicioId]),'''') AS [demandantes]
			,ep.[descripcion] as etapa
			,tp.[descripcion]as tipo
			
	FROM	[dbo].[tbl_Proceso] p,
			[dbo].[tbl_Servicio] s,
			[dbo].[tbl_TipoProceso] tp,
			[dbo].[tbl_EtapaProceso] ep
	WHERE   
			s.[servicioId]=p.[procesoId] 
			AND
			ep.[etapaProcesoId]=p.[etapaProcesoId] 
			AND
			tp.[tipoProcesoId]=ep.[tipoProcesoId]
			AND 
			[codigoServicio] like ''%'' + @varParametroBusqueda + ''%'' 
			AND 
			s.[tipoServicio] = ''Proceso''
			AND 
			(@bitTienePermiso = 1
			 OR
			(@bitTienePermiso = 0 
			AND s.[servicioId] IN (SELECT [id] FROM @tblServicios)))
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetProcesoById]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetProcesoById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 21/03/2012
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetProcesoById]
	@intProcesoId			int
AS
BEGIN
	SET NOCOUNT ON;

    SELECT
			[ProcesoId]
			,[codigoServicio]
			,p.[etapaProcesoId]
			,[numeroExpediente]
			,[montoDemanda]
			,ISNULL([fechaInicio],'''') AS [fechaInicio]
			,[fechaEntregaEstimada]			
			,[fechaFin]
			,[observacion]
			,[estado]
			,ISNULL([fechaEntrega],'''') AS [fechaEntrega]
			,ISNULL([entregadoA],'''') AS [entregadoA]
			,ISNULL([observacionEntrega],'''') AS [observacionEntrega]
			,ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'''') AS [responsables]
			,ISNULL([dbo].[uf_GetDemandadosList](s.[servicioId]),'''') AS [demandados]
			,ISNULL([dbo].[uf_GetDemandantesList](s.[servicioId]),'''') AS [demandantes]
			,ep.[descripcion] as etapa
			,tp.[descripcion]as tipo
			
	FROM	[dbo].[tbl_Proceso] p,
			[dbo].[tbl_Servicio] s,
			[dbo].[tbl_TipoProceso] tp,
			[dbo].[tbl_EtapaProceso] ep
	WHERE   
			s.[servicioId]=p.[procesoId] 
			AND
			ep.[etapaProcesoId]=p.[etapaProcesoId] 
			AND
			tp.[tipoProcesoId]=ep.[tipoProcesoId]
			AND
			[ProcesoId]=@intProcesoId
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteBySearch]    Script Date: 06/21/2012 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTramiteBySearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 08/11/2011
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetTramiteBySearch]
	@varParametroBusqueda	NVARCHAR(4000),
	@varEstados				VARCHAR(150),
	@bitTienePermiso		BIT,
	@intUsuarioId			INT
AS
BEGIN

	SET NOCOUNT ON;
    IF(@varParametroBusqueda IS NULL OR @varParametroBusqueda = '''')
		SET @varParametroBusqueda = ''''
		
	DECLARE @tblEstados AS TABLE([estado] VARCHAR(15))
	INSERT INTO @tblEstados
		SELECT [splitvalue]
		FROM [dbo].[uf_SplitString](@varEstados, '','')
		
	DECLARE @tblServicios AS TABLE([id] INT)
	IF @bitTienePermiso = 0
	BEGIN
	
		INSERT INTO @tblServicios ([id])
			SELECT [servicioId] FROM [dbo].[tbl_Responsable] WHERE [administrativoId] = @intUsuarioId AND [estado] = ''Activo''
	END
	
	SELECT DISTINCT
			[tramiteId]
			,[codigoServicio]
			,t.[tipoTramiteId] 
			,s.[fechaInicio]
			,[fechaEntregaEstimada]	
			,ISNULL(s.[fechaFin],'''') AS [fechaFin]
			,[observacion]
			,s.[estado]
			,ISNULL([fechaEntrega],'''') AS [fechaEntrega]
			,ISNULL([entregadoA],'''') AS [entregadoA]
			,ISNULL([observacionEntrega],'''') AS [observacionEntrega]
			,ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'''') AS [responsables]
			,ISNULL([dbo].[uf_GetInvolucradosList](s.[servicioId]),'''') AS [involucrados]
			,tp.[descripcion]AS tipo
			,ct.[descripcion]AS categoria
	FROM	[dbo].[tbl_Tramite] t
		JOIN [dbo].[tbl_Servicio] s ON s.[servicioId] = t.[tramiteId] AND s.[tipoServicio] = ''Tramite''
		JOIN [dbo].[tbl_TipoTramite] tp ON tp.[tipoTramiteId] = t.[tipoTramiteId]
		JOIN [dbo].[tbl_CategoriaTramite] ct ON ct.[categoriaTramiteId] = tp.[categoriaTramiteId] 
		JOIN [dbo].[tbl_Involucrado] i ON i.[servicioId] = s.[servicioId]
		JOIN [dbo].[uv_Involucrados] inv ON inv.[involucradoId] = i.[involucradoId]
		JOIN [dbo].[tbl_Responsable] r ON  r.[servicioId] = s.[servicioId]
		JOIN [dbo].[uv_Responsables] adm ON adm.[responsableId] = r.[responsableId]
	WHERE   
			(inv.[nombre] LIKE @varParametroBusqueda + ''%''
			OR adm.[nombre] LIKE @varParametroBusqueda + ''%''
			OR s.[codigoServicio] LIKE ''%'' + @varParametroBusqueda + ''%''  
			OR tp.[descripcion] LIKE @varParametroBusqueda + ''%'')
			AND s.[estado] IN (SELECT [estado] FROM @tblEstados)
			AND (@bitTienePermiso = 1 OR
				(@bitTienePermiso = 0 AND s.[servicioId] IN (SELECT [id] FROM @tblServicios)))

END

' 
END
GO

DELETE FROM [dbo].[tbl_Version]
GO

INSERT INTO [dbo].[tbl_Version]
           ([versionMayor]
           ,[versionMenor]
           ,[release])
     VALUES
           (1
           ,2
           ,0)
GO